using System;
using System.IO;
using System.Text;
using System.Reflection;
using System.Collections.Generic;

using Open.Data.Persistence.Hashes;

namespace Open.Data.Persistence.SqlServer
{
    //Copies row data to a persisted object and vice versa.
    public sealed class SqlFormatter : Formatter
    {
        //Property variables
        private Command _command;
        private Concurrency _concurrency;
        private bool _withConcurrency;
        private bool _withInheritance;
        private bool _withKeys;
        private bool _withIdentity;
        private bool _output;
        private string _prefix;
        private Ordinals _ordinals;
        private LabelHash _label;
        private int _chunkSize;

        private System.Data.SqlClient.SqlDataReader _reader;

        //Working variables
        private System.Data.DataTable _schema;
        private int _columnNameOrdinal;
        private int _baseTableNameOrdinal;
        private int _ordinalOrdinal;

        #region Interface

        //Constructors
        public SqlFormatter()
        {
            _withConcurrency = false;
            _withInheritance = false;
            _withKeys = false;
            _withIdentity = false;
            _output = false;
            _prefix = "";
            _chunkSize = 8040;
        }

        public SqlFormatter(SqlCommand command, Concurrency concurrency)
        {
            Command = command;
            Concurrency = concurrency;

            _withConcurrency = false;
            _withInheritance = false;
            _withKeys = false;
            _withIdentity = false;
            _output = false;
            _prefix = "";
            _chunkSize = 8040;
        }

        //Properties
        public override Command Command
        {
            get
            {
                return _command;
            }
            set
            {
                if (value != null && !(value is SqlCommand)) throw new FormatterException("Command provided has to be of type SqlCommand.");
                _command = value;
            }
        }

        public override Concurrency Concurrency
        {
            get
            {
                return _concurrency;
            }
            set
            {
                _concurrency = value;
            }
        }

        public System.Data.SqlClient.SqlDataReader Reader
        {
            get
            {
                return _reader;
            }
            set
            {
                _reader = value;

                //Set up ordinal information
                if (_reader != null)
                {
                    _schema = _reader.GetSchemaTable();
                    _baseTableNameOrdinal = _schema.Columns["BaseTableName"].Ordinal;
                    _columnNameOrdinal = _schema.Columns["ColumnName"].Ordinal;
                    _ordinalOrdinal = _schema.Columns["ColumnOrdinal"].Ordinal;
                }
            }
        }

        public bool WithConcurrency
        {
            get
            {
                return _withConcurrency;
            }
            set
            {
                _withConcurrency = value;
            }
        }

        public bool WithInheritance
        {
            get
            {
                return _withInheritance;
            }
            set
            {
                _withInheritance = value;
            }
        }

        public bool WithKeys
        {
            get
            {
                return _withKeys;
            }
            set
            {
                _withKeys = value;
            }
        }

        public bool WithIdentity
        {
            get
            {
                return _withIdentity;
            }
            set
            {
                _withIdentity = value;
            }
        }

        public bool Output
        {
            get
            {
                return _output;
            }
            set
            {
                _output = value;
            }
        }

        public string Prefix
        {
            get
            {
                return _prefix;
            }
            set
            {
                _prefix = value;
            }
        }


        public Ordinals Ordinals
        {
            get
            {
                return _ordinals;
            }
            set
            {
                _ordinals = value;
            }
        }

        public LabelHash Label
        {
            get
            {
                return _label;
            }
            set
            {
                _label = value;
            }
        }

        public int ChunkSize
        {
            get
            {
                return _chunkSize;
            }
            set
            {
                _chunkSize = value;
            }
        }

        public static void AppendQueryParameterValues(StringBuilder builder, IQuery query, Dictionary<IParameter, string> parameters)
        {
            //Append parameters for the conditions, including any recursive sub conditions
            if (query.Where != null && query.Where.Count > 0) AppendQueryParameterValueDetails(builder, query.Where, parameters);

            //Append any parameters for sub queries
            if (query.SubQueries != null && query.SubQueries.Count > 0)
            {
                foreach (IQuery subquery in query.SubQueries)
                {
                    AppendQueryParameterValues(builder, subquery, parameters);
                }
            }
        }

        //Ensures that a unique name is returned for the list of parameter strings provided
        //Adds the name to the parameters list
        public static string AddUniqueParameter(IParameter parameter, Dictionary<IParameter, string> parameters)
        {
            if (parameters.ContainsKey(parameter)) return parameters[parameter];

            //Loop through the strings and pick the next 
            
            int count = 0;
            string field = parameter.Name;
            string key = field;

            while (parameters.ContainsValue(key))
            {
                count++;
                key = field + count.ToString();
            }

            parameters.Add(parameter, key);
            return key;
        }

        //Move the values from the object supplied to the context
        public override void Encode(IPersistable graph, Type type)
        {
            SqlCacheItem cache = SqlProvider.Instance.ReflectionCache.Lookup(type) as SqlCacheItem;

            bool flag = false;

            if (WithInheritance && !cache.IsUnderlying)
            {
                Encode(graph, cache.BaseType);
                flag = true;
            }

            //Check for command
            if (Command == null) throw new FormatterException("Command not set. The graph cannot be encoded.");

            SqlCommand command = Command as SqlCommand;
            StringBuilder builder = command.Builder;
            Concurrency concurrency = Concurrency;

            if (WithConcurrency && concurrency == null) throw new FormatterException("The object could not be encoded because concurrency information was not provided.");

            //Add key parameters if required
            if (cache.IsUnderlying || !WithInheritance)
            {
                foreach (PropertyInfo prop in cache.GetKeyProperties())
                {
                    if (!cache.IsIdentity || WithIdentity)
                    {
                        if (flag) builder.Append(",");

                        //Get the parameter
                        builder.Append("@");
                        builder.Append(GetPropertyName(prop, ""));
                        builder.Append("=");

                        if (Output)
                        {
                            builder.Append("@_output");
                        }
                        else
                        {
                            EncodeProperty(builder, prop.PropertyType, prop.GetValue(graph, null));
                        }

                        flag = true;
                    }
                }
            }

            foreach (PropertyInfo prop in cache.Properties)
            {
                if (prop.CanRead)
                {
                    if (flag) builder.Append(",");

                    //Get the name
                    string name = GetPropertyName(prop, ".");

                    //Get the parameter
                    builder.Append("@");
                    builder.Append(GetPropertyName(prop, ""));
                    builder.Append("=");

                    EncodeProperty(builder, prop.PropertyType, prop.GetValue(graph, null));

                    flag = true;

                    //Add a concurrency value if required
                    if (WithConcurrency && Deployment.GetTypeConcurrency(prop.PropertyType))
                    {
                        builder.Append(",@");
                        builder.Append(GetPropertyName(prop, ""));
                        builder.Append("Old=");

                        bool encoded = false;

                        //Encode value properties
                        foreach (PropertyInfo valueprop in cache.ValueProperties)
                        {
                            if (valueprop == prop)
                            {
                                //Check if value has changed
                                object newvalue = valueprop.GetValue(graph, null);

                                //Set the old value up from concurrency if enabled and available
                                //Concurrency may not be in collection as it may not be required for some fields
                                object oldvalue = newvalue;
                                if (concurrency.Enabled && concurrency.ContainsKey(name)) oldvalue = concurrency[name];

                                //Compare objects by converting them to strings to do a value comparison
                                //if (oldvalue == null || oldvalue.ToString() == newvalue.ToString())

                                //Compare old and new values using equals method
                                if (oldvalue == null || oldvalue.Equals(newvalue))
                                {
                                    builder.Append("NULL");
                                }
                                else
                                {
                                    EncodeProperty(builder, valueprop.PropertyType, oldvalue);
                                }

                                encoded = true;
                                break;
                            }
                        }

                        //Encode object properties
                        if (!encoded)
                        {
                            foreach (PropertyInfo objectprop in cache.ReferenceProperties)
                            {
                                if (objectprop == prop)
                                {
                                    PropertyInfo encodeProp = objectprop;

                                    //Check if value has changed
                                    object newvalue = objectprop.GetValue(graph, null);

                                    //Get the key value if an object property    
                                    if (newvalue != null)
                                    {
                                        CacheItem refcache = SqlProvider.Instance.ReflectionCache.Lookup(objectprop.PropertyType);
                                        PropertyInfo refprop = refcache.PrimaryKey;
                                        newvalue = refprop.GetValue(newvalue, null);
                                        encodeProp = refprop;
                                    }

                                    //Set the old value up from concurrency if enabled and available
                                    //Concurrency may not be in collection as it may not be required for some fields
                                    object oldvalue = newvalue;
                                    if (concurrency.Enabled && concurrency.ContainsKey(name)) oldvalue = concurrency[name];

                                    //Compare objects by converting them to strings to do a value comparison
                                    if (oldvalue == null || oldvalue.Equals(newvalue))
                                    {
                                        builder.Append("NULL");
                                    }
                                    else
                                    {
                                        EncodeProperty(builder, encodeProp.PropertyType, oldvalue);
                                    }

                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }

        //Delimit value types
        public static void EncodeProperty(StringBuilder builder, Type type, object value)
        {
            //Get the underlying type from the nullable type
            if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                type = type.GetGenericArguments()[0];
            }

            //Delimit the value according to the type and value
            if (value == null)
            {
                builder.Append("NULL");
            }
            else if (type.IsEnum)
            {
                builder.Append(Convert.ToInt32(value).ToString());
            }
            else if (type == typeof(DateTime))
            {
                DateTime datetime = (DateTime)value;
                builder.Append("'");
                builder.Append(datetime.ToLongDateString());
                builder.Append(" ");
                builder.Append(datetime.ToLongTimeString());
                builder.Append("'");
            }
            else if (type == typeof(string))
            {
                builder.Append("'");
                builder.Append(value.ToString().Replace("'", "''"));
                builder.Append("'");
            }
            else if (type == typeof(Guid))
            {
                builder.Append("'");
                builder.Append(value.ToString());
                builder.Append("'");
            }
            else if (typeof(IPersistable).IsAssignableFrom(type))
            {
                CacheItem refcache = SqlProvider.Instance.ReflectionCache.Lookup(type);
                PropertyInfo refprop = refcache.PrimaryKey;

                EncodeProperty(builder, refprop.PropertyType, refprop.GetValue(value, null));
            }
            else if (type == typeof(Stream))
            {
                //Make zero length hex for now
                builder.Append("0x");
            }
            else
            {
                builder.Append(value.ToString());
            }
        }

        public void EncodeBinary(Stream stream)
        {
            SqlCommand command = Command as SqlCommand;
            StringBuilder builder = command.Builder;

            long end = stream.Position + _chunkSize;
            int i = 0;

            while (stream.Position <= end && i != -1)
            {
                i = stream.ReadByte();
                if (i != -1) builder.Append(i.ToString("X2")); //Converts to hex with minimum 2 places
            }
        }

        //Populate the object graph with values from the context
        public override void Decode(IPersistable graph, Type type)
        {
            //Check for command
            if (Command == null) throw new FormatterException("Command not set. The graph cannot be decoded.");

            //Reset Concurrency
            if (Concurrency != null) Concurrency.Reset();

            //Set up ordinals
            if (Ordinals == null) Ordinals = new Ordinals(type);
            if (Label == null) Label = new TypeLabelHash(type);

            CacheItem cache = SqlProvider.Instance.ReflectionCache.Lookup(type);

            Concurrency concurrency = Concurrency;
            System.Data.SqlClient.SqlDataReader reader = Reader;

            //Get the keys
            PropertyInfo[] keys = cache.GetKeyProperties();
            string tableName = Common.GetTypeName(cache.PersistType, Prefix);
            string name = GetPropertyName(keys[0], ".");

            //Decode underlying objects
            if (WithInheritance && !cache.IsUnderlying)
            {
                Label = new TypeLabelHash(cache.BaseType, Label);
                Decode(graph, cache.BaseType);
                Label = Label.Parent;
            }

            //Determine if key properties must also be decoded
            if (WithKeys)
            {
                //Key properties cant be objects or nulls
                foreach (PropertyInfo prop in keys)
                {
                    //Get the original property table name if a view
                    if (cache.IsView) tableName = Common.GetTypeName(cache.GetOriginalProperty(prop).ReflectedType, Prefix);

                    name = GetPropertyName(prop, ".");
                    int ordinal = GetOrdinal(tableName, name, Ordinals[Label]);

                    if (reader.GetValue(ordinal) != DBNull.Value)
                    {
                        //Get value
                        if (prop.PropertyType.IsEnum)
                        {
                            prop.SetValue(graph, Enum.Parse(prop.PropertyType, reader.GetInt32(ordinal).ToString()), null);
                        }
                        else
                        {
                            prop.SetValue(graph, reader.GetValue(ordinal), null);
                        }
                    }
                }
            }

            //Look at all properties for the object and update context
            PropertyInfo[] properties = cache.Properties;

            foreach (PropertyInfo prop in properties)
            {
                //Get the original property table name if a view
                if (cache.IsView) tableName = Common.GetTypeName(cache.GetOriginalProperty(prop).ReflectedType, Prefix);

                name = GetPropertyName(prop, ".");
                int ordinal = GetOrdinal(tableName, name, Ordinals[Label]);

                object propvalue;
                object concurrencyvalue;

                //Check for null value
                if (prop.PropertyType.IsEnum && !reader.IsDBNull(ordinal))
                {
                    propvalue = Enum.Parse(prop.PropertyType, reader.GetInt16(ordinal).ToString());
                    concurrencyvalue = propvalue;
                }
                else if (typeof(IPersistable).IsAssignableFrom(prop.PropertyType))
                {
                    if (reader.IsDBNull(ordinal))
                    {
                        concurrencyvalue = null;
                        propvalue = null;
                    }
                    else
                    {
                        concurrencyvalue = reader.GetValue(ordinal);

                        //Create a new object if required, otherwise overwrite the existing object
                        propvalue = prop.GetValue(graph,null);

                        if (propvalue == null) propvalue = ActivateObject(prop.PropertyType, concurrencyvalue);

                        //Set just the key of the new object so it can be loaded later
                        CacheItem propcache = SqlProvider.Instance.ReflectionCache.Lookup(prop.PropertyType);
                        PropertyInfo refprop = propcache.PrimaryKey;
                        refprop.SetValue(propvalue, concurrencyvalue, null);
                    }
                }
                else if (typeof(Stream).IsAssignableFrom(prop.PropertyType))
                {
                    if (reader.IsDBNull(ordinal))
                    {
                        concurrencyvalue = null;
                        propvalue = null;
                    }
                    else
                    {
                        concurrencyvalue = null;
                        Stream stream;

                        if (prop.PropertyType.IsAbstract)
                        {
                            stream = new MemoryStream();
                        }
                        else
                        {
                            stream = (Stream)Activator.CreateInstance(prop.PropertyType);

                            if (!stream.CanWrite) throw new FormatterException("Property " + prop.Name + " does not support writing of data.");
                        }

                        //Read into a byte array then into a stream
                        Byte[] b = new Byte[(reader.GetBytes(ordinal, 0, null, 0, int.MaxValue))];
                        reader.GetBytes(ordinal, 0, b, 0, b.Length);
                        stream.Write(b, 0, b.Length);
                        stream.Position = 0;

                        propvalue = stream;
                    }
                }
                else
                {
                    propvalue = reader.GetValue(ordinal);
                    concurrencyvalue = propvalue;

                    if (propvalue == System.DBNull.Value)
                    {
                        propvalue = null;
                        concurrencyvalue = null;
                    }
                }

                //Update value
                prop.SetValue(graph, propvalue, null);

                //Update the concurrency provider
                if (concurrency != null && concurrency.Enabled) concurrency.Add(name, concurrencyvalue);
            }

            //Set the concurency to loaded
            if (Concurrency != null) Concurrency.Status = ConcurrencyStatus.Loaded;

            //Decode object properties
            if (cache.HasObjects)
            {
                properties = cache.ReferenceProperties;

                foreach (PropertyInfo prop in properties)
                {
                    IPersistable persistable = prop.GetValue(graph, null) as IPersistable;
                    if (persistable != null)
                    {
                        Concurrency = null;
                        
                        if (persistable is IConcurrency) Concurrency = (persistable as IConcurrency).GetConcurrency();

                        //Make sure that this object hasnt already been decoded
                        if (Concurrency == null || Concurrency.Status == ConcurrencyStatus.None)
                        {
                            Label = new PropertyLabelHash(prop, Label);
                            Decode(persistable, prop.PropertyType);
                            Label = Label.Parent;
                        }
                    }
                }
            }
        }

        public override void Reset()
        {
            _ordinals = null;
            _label = null;
        }

        public int GetOrdinal(string table, string name, int occurence)
        {
            string key = string.Empty;
            int count = 0;

            //Loop through and get the column for the table, adding to cache if required
            foreach (System.Data.DataRow row in _schema.Rows)
            {
                var baseTableName = row[_baseTableNameOrdinal].ToString(); //Table name is blank for aggregate fields
                if ((baseTableName == table || baseTableName == "" )&& row[_columnNameOrdinal].ToString() == name)
                {
                    //Add one to the occurence counter
                    count++;

                    if (count == occurence)
                    {
                        int ordinal = int.Parse(row[_ordinalOrdinal].ToString());
                        return ordinal;
                    }
                }
            }

            throw new FormatterException("Ordinal not found for Table " + table + ", column " + name + ". Make sure the source data reader is opened with CommandBehavior.KeyInfo");
        }

        #endregion

        #region Implementation

        //Append the parameters needed to pass through literal comparison values
        private static void AppendQueryParameterValueDetails(StringBuilder builder, Conditions conditions, Dictionary<IParameter, string> parameters)
        {
            //Loop through conditions recursively
            foreach (Condition condition in conditions)
            {
                foreach (Expression expression in condition.GetExpressions())
                {
                    foreach (IParameter parameter in expression.GetParameters())
                    {
                        //Get the name from the previously generated paramter list
                        if (parameters.Count > 0) builder.Append(",");

                        builder.Append("@");
                        builder.Append(AddUniqueParameter(parameter, parameters));
                        builder.Append(" ");
                        builder.Append("=");

                        EncodeProperty(builder, parameter.Type, parameter.GetValue());
                    }
                }
                
                if (condition.SubConditions != null) AppendQueryParameterValueDetails(builder, condition.SubConditions, parameters);
            }
        }

        #endregion
    }
}
